import db from '../config/database.js';

// Get all categories
export const getAllCategories = (req, res) => {
  try {
    const categories = db.prepare(`
      SELECT c.*, 
        (SELECT COUNT(*) FROM products WHERE category_id = c.id AND status = 1) as product_count
      FROM categories c
      ORDER BY c.display_order ASC, c.id DESC
    `).all();

    res.json({
      success: true,
      data: categories
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
};

// Get category by ID
export const getCategoryById = (req, res) => {
  try {
    const { id } = req.params;
    const category = db.prepare('SELECT * FROM categories WHERE id = ?').get(id);

    if (!category) {
      return res.status(404).json({
        success: false,
        message: 'Category not found'
      });
    }

    res.json({
      success: true,
      data: category
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
};

// Create category
export const createCategory = (req, res) => {
  try {
    const { name, name_en, description, display_order } = req.body;

    const stmt = db.prepare(`
      INSERT INTO categories (name, name_en, description, display_order)
      VALUES (?, ?, ?, ?)
    `);

    const result = stmt.run(name, name_en, description || '', display_order || 0);

    res.status(201).json({
      success: true,
      message: 'Category created successfully',
      data: { id: result.lastInsertRowid }
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
};

// Update category
export const updateCategory = (req, res) => {
  try {
    const { id } = req.params;
    const { name, name_en, description, display_order } = req.body;

    const stmt = db.prepare(`
      UPDATE categories 
      SET name = ?, name_en = ?, description = ?, display_order = ?, updated_at = CURRENT_TIMESTAMP
      WHERE id = ?
    `);

    const result = stmt.run(name, name_en, description, display_order, id);

    if (result.changes === 0) {
      return res.status(404).json({
        success: false,
        message: 'Category not found'
      });
    }

    res.json({
      success: true,
      message: 'Category updated successfully'
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
};

// Delete category
export const deleteCategory = (req, res) => {
  try {
    const { id } = req.params;

    const stmt = db.prepare('DELETE FROM categories WHERE id = ?');
    const result = stmt.run(id);

    if (result.changes === 0) {
      return res.status(404).json({
        success: false,
        message: 'Category not found'
      });
    }

    res.json({
      success: true,
      message: 'Category deleted successfully'
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: error.message
    });
  }
};

